<html>
<body>
	<%@ page language="java" import="java.sql.*"%>
	<%
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cse132b","cse132blogin", "1234567890");
			String action = request.getParameter("action");
			// Check if an insertion is requested
			if (action != null && action.equals("insert")) {
				PreparedStatement pstmt;
				pstmt = conn.prepareStatement("INSERT INTO enroll (student_id,section_id,units) VALUES (?,?,?)");
				pstmt.setInt(1, Integer.parseInt(request.getParameter("studentid")));
				pstmt.setInt(2, Integer.parseInt(request.getParameter("sectionid")));
				pstmt.setInt(3, Integer.parseInt(request.getParameter("units")));
				int rowCount = pstmt.executeUpdate();
				out.println("Successfully enrolled student.<br/>");
			}

			
			if ( request.getParameter("sectionid") == null ) {
				Statement sections = conn.createStatement();
				ResultSet rsSections = sections.executeQuery("SELECT * FROM sections s LEFT JOIN classes cl ON (s.class_id = cl.class_id) LEFT JOIN courses co ON (cl.course_id = co.course_id) WHERE year = 2014");
	%>
	
<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>	
	<table border="0">
		<tr>
			<th>Select a class for Spring 2014</th>
		</tr>
		<tr>
			<th>Section ID</th>
			<th>Course Name</th>
		</tr>
		<%
		while (rsSections.next()) {
		%>
		
		<tr>
			<td align="center">
				<a href="enrollment.jsp?sectionid=<%=rsSections.getInt("section_id") %>"><%=rsSections.getInt("section_id") %></a>
			</td>
			<td>
				<a href="enrollment.jsp?sectionid=<%=rsSections.getInt("section_id") %>"><%=rsSections.getString("department_abbreviation") %> <%=rsSections.getString("course_number") %></a>
			</td>
			
		</tr>
		<%
		}
		%>
	</table>
	</td>
</tr>
</table>
	<% 
			} else {
				Statement students = conn.createStatement();
				ResultSet rsStudents = students.executeQuery("SELECT * FROM students");
				PreparedStatement section = conn.prepareStatement("SELECT * FROM sections s LEFT JOIN classes cl ON (s.class_id = cl.class_id) LEFT JOIN courses co ON (cl.course_id = co.course_id) WHERE year = 2014 AND s.section_id = ?");
				section.setInt(1, Integer.parseInt(request.getParameter("sectionid")));
				ResultSet rsSection = section.executeQuery();
				rsSection.next();				
	%>
	<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
	<table border="0">
		<tr>
			<td valign="top">Enrollment Page</td>
		</tr>
		<tr>
			<td><%=rsSection.getString("department_abbreviation") %> <%=rsSection.getString("course_number") %></td>
		</tr>
		<tr>
			<table border="1">
				<tr>
					<th>Student*</th>
					<th>Units*</th>
				</tr>
				<tr>
					<form action="enrollment.jsp" method="get">
						<input type="hidden" value="insert" name="action">
						<input type="hidden" value="<%=request.getParameter("sectionid") %>" name="sectionid">
						<td>
							<select name="studentid">
								<%
								while(rsStudents.next()) {
								%>
									<option value="<%=rsStudents.getInt("student_id") %>"><%=(rsStudents.getString("first_name") + (rsStudents.getString("middle_name") != null ? (" " + rsStudents.getString("middle_name")) : "") + " " + rsStudents.getString("last_name")) %></option>
								<%
								}
								%>
							</select>
						</td>
						<td>
							<select name="units">
								<%
									int minunits = rsSection.getInt("min_units");
									if (!rsSection.wasNull()) {
										for (int i = minunits;i<rsSection.getInt("max_units");i++) {
								%>
								<option value="<%=i %>"><%=i %></option>
								<%
										}
									}
								%>
								<option value="<%=rsSection.getInt("max_units") %>"><%=rsSection.getInt("max_units") %></option>
							</select>
						</td>
						<td><input type="submit"></input></td>
					</form>
				</tr>
			</table>
		</tr>
	</table>
	<%
			}
		} catch (SQLException ex) {
			out.print("SQLException: " + ex.getMessage() + "<br/>");
			out.print("SQLState: " + ex.getSQLState() + "<br/>");
			out.print("VendorError: " + ex.getErrorCode() + "<br/>");
		}
	%>

</td>
</tr>
</table>
</body>
</html>